package org.compiere.process;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.logging.Level;

import org.compiere.model.MClient;
import org.compiere.model.MMailText;
import org.compiere.model.MUser;
import org.compiere.model.X_C_BPartner;
import org.compiere.model.X_R_MailText;
import org.compiere.util.DB;
import org.compiere.util.EMail;


public class CMS_InformTask extends SvrProcess {

	int P_AD_User_ID = 1000000;
	
	@Override
	protected void prepare() {

	}

	@Override
	protected String doIt() throws Exception {
		
		ArrayList<Integer> contracts = getContracts();
		
		for(int i = 0; i < contracts.size(); i++)
		{
			generateReminder(contracts.get(i));
		}
		
		return "Процесс рассылки уведомлений о согласовании договоров выполнен успешно";
	}
	
	private void generateReminder(int CMS_Contract_ID)
	{
		ArrayList<Integer> signers = getSigners(CMS_Contract_ID);
		
		if(signers.size() == 0)
			return;
		
		String label = getLabel(CMS_Contract_ID);
		String message = getContractInfo(CMS_Contract_ID);
		
		try
		{
			for(int i = 0; i < signers.size(); i++)
			{
				sendMail(signers.get(i), P_AD_User_ID, label, message);
			}
		}
		catch(Exception e){log.log(Level.SEVERE, "CMS_Agreement: " + e.toString());}		
	}
	
	private String getContractInfo(int CMS_Contract_ID)
	{
		String info = "";
		
		String sql = "SELECT DocumentNo, Description  FROM CMS_Contract WHERE DocStatus = 'WC' AND CMS_Contract_ID = " + CMS_Contract_ID;
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try
		{
			pstmt = DB.prepareStatement(sql, get_TrxName());
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				info = "Вам необходимо согласовать договор номер - " + rs.getString(1) + "\n\r" + "Предмет договора: " + rs.getString(2);
			}
		}
		catch(Exception e) {log.log(Level.SEVERE, "CMS_Agreement: " + e.toString());}
		finally{DB.close(rs,pstmt); rs = null; pstmt = null;}
		
		return info;
	}
	
	private String getLabel(int CMS_Contract_ID)
	{
		String sqlNameAgreement = "SELECT MAX(CMS_Agreement_ID), name FROM CMS_Agreement WHERE CMS_Contract_ID = " + CMS_Contract_ID + "GROUP BY name";
		
		String name = "Согласование договора. ";
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String label = null;
		
		try
		{
			pstmt = DB.prepareStatement(sqlNameAgreement, get_TrxName());
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				label = rs.getString(2);
			}
		}
		catch(Exception e) {log.log(Level.SEVERE, "CMS_Agreement: " + e.toString());}
		finally{DB.close(rs,pstmt); rs = null; pstmt = null;}

		return name + (label == null ? "" : label);
	}
	
	private ArrayList<Integer> getContracts()
	{
		ArrayList<Integer> contracts = new ArrayList<Integer>();
		
		StringBuffer sql = new StringBuffer();
		
		sql.append("SELECT CMS_Contract_ID FROM CMS_Contract WHERE DocStatus = 'WC'");
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			
			while(rs.next()) 
			{
				contracts.add(rs.getInt("CMS_Contract_ID"));
			}
		}
		catch(Exception e) {log.log(Level.SEVERE , "CMS_Agreement: " + e.toString());}
		finally{DB.close(rs, pstmt); rs = null; pstmt = null;}
		
		return contracts;
	}
		
	private ArrayList<Integer> getSigners(int CMS_Contract_ID)
	{
		ArrayList<Integer> signers = new ArrayList<Integer>();
		
		StringBuffer sql = new StringBuffer();
		
		sql.append("SELECT MAX(a.CMS_Agreement_ID) FROM CMS_Agreement a ");
		sql.append("INNER JOIN CMS_Contract c ON a.CMS_Contract_ID = c.CMS_Contract_ID ");
		sql.append("WHERE c.CMS_Contract_ID = " + CMS_Contract_ID);
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		int CMS_Agreement_ID = DB.getSQLValue(get_TrxName(), sql.toString());
		
		if(CMS_Agreement_ID == -1)
			return signers;
		
		sql.setLength(0);
		
		sql.append("SELECT AD_User_ID FROM CMS_Agreement WHERE Sign_Y = 'N' AND CMS_Agreement_ID = " + CMS_Agreement_ID);
		
		int headSigner = DB.getSQLValue(get_TrxName(), sql.toString());
		
		if(headSigner != -1)
		{
			signers.add(headSigner);
			return signers;
		}
		
		sql.setLength(0);
		
		sql.append("SELECT l.Signer::INTEGER ");
		sql.append("FROM CMS_Agreement_Line l ");
		sql.append("INNER JOIN CMS_Agreement a ON l.CMS_Agreement_ID = a.CMS_Agreement_ID ");
		sql.append("WHERE a.CMS_Agreement_ID = " + CMS_Agreement_ID);
		sql.append(" AND l.SIGN_Y = 'N' AND l.SIGN_N = 'N'");
		
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			
			while(rs.next())
			{
				signers.add(rs.getInt(1));
			}
		}
		catch(Exception e) {log.log(Level.SEVERE, "CMS_Agreement: " + e.toString());}
		finally{DB.close(rs, pstmt); rs = null; pstmt = null;}
		
		return signers;
	}
	
	private boolean sendMail(int userToID, int userFromID, String label, String message) throws Exception
	{
		MUser userFrom = new MUser(getCtx(), userFromID, get_TrxName());
		MUser userTo = new MUser(getCtx(), userToID, get_TrxName());

		MClient client = new MClient(getCtx(), getAD_Client_ID(), get_TrxName());
		
		X_R_MailText rText = new X_R_MailText(getCtx(), null, get_TrxName());		
		
		StringBuffer sql = new StringBuffer();
		sql.setLength(0);
	
		sql.append("select c_bpartner_id from ad_user where ad_user_id = " + userFromID);
		int C_BPartner_ID = DB.getSQLValue(get_TrxName(), sql.toString());
		
		if(C_BPartner_ID == -1)
			throw new Exception("Ошибка получения имени отправителя");
		
		X_C_BPartner bpartner = new X_C_BPartner(getCtx(), C_BPartner_ID, get_TrxName());
		
		rText.setName(bpartner.getName());
		rText.setMailHeader(label);
		rText.setMailText(message);
		
		if(!rText.save())
			throw new Exception("Невозможно сохранить сообщение");
		
		MMailText mailText = new MMailText(getCtx(), rText.get_ID(), get_TrxName());
		
		EMail mail = client.createEMail(userFrom, userTo, mailText.getMailHeader(), mailText.getMailText());	
		
		try {
			EMail.SENT_OK.equals(mail.send());
		}
		catch(Exception e){}
		
		return true;
	}

}
